package com.huitongmaoyi.ai.dao;

import com.huitongmaoyi.ai.entity.ChatMessage;
import com.huitongmaoyi.ai.entity.ChatSession;
import com.huitongmaoyi.ai.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

/**
 * 聊天消息数据访问类
 */
public class ChatDAO {

    /**
     * 创建聊天会话（点对点）
     */
    public long createSession(long userId, String customerId, Long otherUserId) throws SQLException {
        String sql = "INSERT INTO chat_sessions (user_id, customer_id, other_user_id) VALUES (?, ?, ?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            stmt.setLong(1, userId);
            stmt.setString(2, customerId);
            stmt.setObject(3, otherUserId);
            stmt.executeUpdate();

            ResultSet rs = stmt.getGeneratedKeys();
            if (rs.next()) {
                return rs.getLong(1);
            }
            throw new SQLException("创建会话失败");
        } finally {
            DBUtil.closeConnection();
        }
    }

    /**
     * 获取或创建会话（避免重复创建会话）
     */
    public long getOrCreateSession(long userId, Long otherUserId) throws SQLException {
        // 查询是否已有会话
        String querySql = "SELECT session_id FROM chat_sessions " +
                "WHERE user_id = ? AND other_user_id = ? " +
                "UNION " +
                "SELECT session_id FROM chat_sessions " +
                "WHERE user_id = ? AND other_user_id = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement queryStmt = conn.prepareStatement(querySql)) {

            queryStmt.setLong(1, userId);
            queryStmt.setLong(2, otherUserId);
            queryStmt.setLong(3, otherUserId);
            queryStmt.setLong(4, userId);

            ResultSet rs = queryStmt.executeQuery();
            if (rs.next()) {
                return rs.getLong("session_id");
            }
        } finally {
            DBUtil.closeConnection();
        }

        // 不存在则创建新会话
        return createSession(userId, null, otherUserId);
    }

    /**
     * 发送点对点消息
     */
    public void sendMessage(long sessionId, String senderType, String senderId, String content) throws SQLException {
        String sql = "INSERT INTO chat_messages (session_id, sender_type, sender_id, content, sent_at, is_read) " +
                "VALUES (?, ?, ?, ?, NOW(), FALSE)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setLong(1, sessionId);
            stmt.setString(2, senderType);
            stmt.setString(3, senderId);
            stmt.setString(4, content);
            stmt.executeUpdate();
        } finally {
            DBUtil.closeConnection();
        }
    }

    /**
     * 发送聊天室消息
     */
    public void sendRoomMessage(long roomId, String senderType, String senderId, String content) throws SQLException {
        String sql = "INSERT INTO chat_messages (room_id, sender_type, sender_id, content, sent_at, is_read) " +
                "VALUES (?, ?, ?, ?, NOW(), FALSE)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setLong(1, roomId);
            stmt.setString(2, senderType);
            stmt.setString(3, senderId);
            stmt.setString(4, content);
            stmt.executeUpdate();
        } finally {
            DBUtil.closeConnection();
        }
    }

    /**
     * 获取会话消息列表
     */
    public List<ChatMessage> getMessages(long sessionId) throws SQLException {
        List<ChatMessage> messages = new ArrayList<>();
        String sql = "SELECT * FROM chat_messages WHERE session_id = ? ORDER BY sent_at ASC";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setLong(1, sessionId);
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                ChatMessage msg = new ChatMessage();
                msg.setMessageId(rs.getLong("message_id"));
                msg.setSessionId(sessionId);
                msg.setSenderType(rs.getString("sender_type"));
                msg.setSenderId(rs.getString("sender_id"));
                msg.setContent(rs.getString("content"));
                msg.setSentAt(rs.getTimestamp("sent_at").toLocalDateTime());
                msg.setRead(rs.getBoolean("is_read"));
                messages.add(msg);
            }
        } finally {
            DBUtil.closeConnection();
        }
        return messages;
    }

    /**
     * 获取用户的所有会话
     */
    public List<ChatSession> getUserSessions(long userId) throws SQLException {
        List<ChatSession> sessions = new ArrayList<>();
        String sql = "SELECT * FROM chat_sessions WHERE user_id = ? ORDER BY updated_at DESC";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setLong(1, userId);
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                ChatSession session = new ChatSession();
                session.setSessionId(rs.getLong("session_id"));
                session.setUserId(userId);
                session.setCustomerId(rs.getString("customer_id"));
                session.setOtherUserId(rs.getLong("other_user_id"));
                session.setCreatedAt(rs.getTimestamp("created_at").toLocalDateTime());
                session.setUpdatedAt(rs.getTimestamp("updated_at").toLocalDateTime());
                sessions.add(session);
            }
        } finally {
            DBUtil.closeConnection();
        }
        return sessions;
    }

    /**
     * 获取指定会话的新消息
     */
    public List<ChatMessage> getNewMessages(long sessionId, long lastMessageId) throws SQLException {
        List<ChatMessage> newMessages = new ArrayList<>();
        String sql = "SELECT * FROM chat_messages " +
                "WHERE session_id = ? AND message_id > ? " +
                "ORDER BY sent_at ASC";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setLong(1, sessionId);
            stmt.setLong(2, lastMessageId);
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                ChatMessage msg = new ChatMessage();
                msg.setMessageId(rs.getLong("message_id"));
                msg.setSessionId(sessionId);
                msg.setSenderType(rs.getString("sender_type"));
                msg.setSenderId(rs.getString("sender_id"));
                msg.setContent(rs.getString("content"));
                msg.setSentAt(rs.getTimestamp("sent_at").toLocalDateTime());
                msg.setRead(rs.getBoolean("is_read"));
                newMessages.add(msg);
            }
        } finally {
            DBUtil.closeConnection();
        }
        return newMessages;
    }

    /**
     * 标记消息为已读
     */
    public void markAsRead(long messageId) throws SQLException {
        String sql = "UPDATE chat_messages SET is_read = TRUE WHERE message_id = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setLong(1, messageId);
            stmt.executeUpdate();
        } finally {
            DBUtil.closeConnection();
        }
    }
}